package com.lq.util.jdbc;

import java.io.Reader;
import java.io.Writer;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import oracle.sql.CLOB;

import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.log4j.Logger;


/**
 * JDBC工厂 
 * @author 吃人的肉
 * QQ:376870344<br>
 * email:liuqingrou@163.com
 */
public class LqJdbcFactory {
	//数据源连接参数
	public   Connection conn = null;
	public  Statement stmt = null;
	public  PreparedStatement pstmt=null;
	protected Logger log = Logger.getLogger(LqJdbcFactory.class);
	public int shiB=0;
	
	/**
	 * 不用传参的CON
	 */
	public LqJdbcFactory(){
		try {
			conn=LqDBOperator.dsWebgame.getConnection();//连接池
		} catch (SQLException e1) {
			log.error(e1);
			e1.printStackTrace();
		}
	}
	public LqJdbcFactory(Connection c){
		conn=c;
	}
	/**
	 * 用传参的CON
	 * @param properties
	 */
	public LqJdbcFactory(String properties){
		LqConfig conf= new LqConfig();
		conf.init(properties);
		try {
			Class.forName(conf.getString("dataSource.driverClassName"));
			conn = DriverManager.getConnection(conf.getString("dataSource.allurl").split("\\*\\*\\*")[0],conf.getString("dataSource.allurl").split("\\*\\*\\*")[1],conf.getString("dataSource.allurl").split("\\*\\*\\*")[2]);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		}
	}
	public LqJdbcFactory(String driverClassName,String url){
		try {
			Class.forName(driverClassName);
			conn = DriverManager.getConnection(url.split("\\*\\*\\*")[0],url.split("\\*\\*\\*")[1],url.split("\\*\\*\\*")[2]);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
		}
	}
	
	/**
	 * 查询《安全》
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public  <T> List<T> find(String sql,Class<T> cls,Object... obj){
		int b=1;//是否关闭连接
		java.sql.ResultSet rs = null;
		ArrayList list = new ArrayList();
		try {
			list = new ArrayList();
			pstmt = conn.prepareStatement(sql);
			if (obj!=null) {
				for (int i = 1; i <= obj.length; i++) {
					pstmt.setObject(i, obj[i-1]);
				}
			}
			rs = pstmt.executeQuery();
			log.info(sql.replaceAll("\r\n", " "));
			ResultSetMetaData rsm = rs.getMetaData();// 获取数据库表结构
			int col = rsm.getColumnCount();// 获取数据库的列数
			while (rs.next()) {
				T t = executeResultSet(cls, rs);
                list.add(t);
			}
			shiB=1;
		}catch(Exception e){
			log.error(e);
			e.printStackTrace();
		}finally{
			if(b==1){
				if (rs!=null) {
					try {
						rs.close();
					} catch (SQLException e) {
						log.error(e);
						e.printStackTrace();
					}
				}
//				close();
			}
		}
		return list;
	}
	
	
	/**
	 * EXEC《安全》
	 * @param sql
	 * @return
	 */
	public  int execute(String sql,Object... obj){
		int b=1;
		try {
			pstmt = conn.prepareStatement(sql);
			if (obj!=null) {
				for (int i=1;i<=obj.length; i++) {
					pstmt.setObject(i, obj[i-1]);
				}
			}
			pstmt.executeUpdate();
			log.info(sql.replaceAll("\r\n", " "));
			shiB=1;
		} catch (SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.error(e1);
				e1.printStackTrace();
			}
			b=0;
			log.error(e);
			e.printStackTrace();
		} finally {
//			close();
		}
		return b;
	}
	

	
	
	
	/**
	 * 查询《安全》
	 * @param sql
	 * @return
	 */
	public  List find(String sql,Object... obj){
		int b=1;//是否关闭连接
		java.sql.ResultSet rs = null;
		ArrayList list = new ArrayList();
		try {
			list = new ArrayList();
			pstmt = conn.prepareStatement(sql);
			if (obj!=null) {
				for (int i = 1; i <= obj.length; i++) {
					pstmt.setObject(i, obj[i-1]);
				}
			}
			rs = pstmt.executeQuery();
			log.info(sql.replaceAll("\r\n", " "));
			ResultSetMetaData rsm = rs.getMetaData();// 获取数据库表结构
			int col = rsm.getColumnCount();// 获取数据库的列数
			while (rs.next()) {
				ListOrderedMap rowData = new ListOrderedMap();
				for (int i = 1; i <= col; i++) {
					if (rsm.getColumnTypeName(i).toUpperCase().equals("BLOB")) {
						b=0;
					}
					if (rsm.getColumnTypeName(i).toUpperCase().equals("CLOB")) {
						b=0;
					} 
					if (rs.getObject(i)==null) {
						rowData.put(rsm.getColumnName(i).toLowerCase(), "");
					}else {
						rowData.put(rsm.getColumnName(i).toLowerCase(), rs.getObject(i));					
					}
				}
				list.add(rowData);
			}
			shiB=1;
		}catch(Exception e){
			log.error(e);
			e.printStackTrace();
		}finally{
			if(b==1){
				if (rs!=null) {
					try {
						rs.close();
					} catch (SQLException e) {
						log.error(e);
						e.printStackTrace();
					}
				}
//				close();
			}
		}
		return list;
	}

	
	
	
	
	/**
	 * 关闭
	 */
	public  void close(){
		if (stmt != null){
			try {
				stmt.close();
			} catch (SQLException e) {
				log.error(e);
				e.printStackTrace();
			}
		}
		if (conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				log.error(e);
				e.printStackTrace();
			}
		}
		System.gc();
	}
	
	
	/**
	 * 分页《安全》<br>
	 * SQL_SERVER_2005分页:SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* from tableName
	 * @param sql
	 * @param pageNumber1 当前页数
	 * @param pageSize 一页所显示的记录数
	 * @param totalCount 总记录数
	 * @return
	 */
	public  Page find(String sql,Integer pageNumber,Integer pageSize,Long totalCount,Object... obj){
		if (pageNumber==null||pageNumber.equals("")) {
			pageNumber=1;
		}
		if (totalCount <= 0) {
			return new Page();
		}
		long totalPageCount = getTotalPageCount(totalCount, pageSize);
		if (pageNumber > totalPageCount) {
			pageNumber = Long.valueOf(totalPageCount).intValue();
		}
		int startIndex = Page.getStartOfPage(pageNumber, pageSize);
		
		String exeSql="";
		if (LqDBOperator.conf.getString("dataSource.driverClassName").toLowerCase().indexOf("mysql")!=-1) {
			exeSql = sql+" limit "+startIndex+","+pageSize;
		}else if (LqDBOperator.conf.getString("dataSource.driverClassName").toLowerCase().indexOf("oracle")!=-1) {
			exeSql = "select * from (select row_.*, rownum rownum_  from (" + sql + ") row_" + " where rownum <= " + (startIndex + pageSize) + ")" + " where rownum_ >"+ startIndex;
		}else if(LqDBOperator.conf.getString("dataSource.driverClassName").toLowerCase().indexOf("sqlserver")!=-1){
			/**
			 * 注解：首先利用Row_number()为table1表的每一行添加一个行号，给行号这一列取名'RowNumber' 在over()方法中将'RowNumber'做了升序排列
			 * 然后将'RowNumber'列 与table1表的所有列 形成一个表A
			 * 重点在where条件。假如当前页(currentPage)是第2页，每页显示10个数据(pageSzie)。那么第一页的数据就是第11-20条
			 * 所以为了显示第二页的数据，即显示第11-20条数据，那么就让RowNumber大于 10*(2-1) 即：页大小*(当前页-1)
			 */
			exeSql="SELECT TOP "+pageSize+" * FROM ( "+sql+" ) as row_ WHERE RowNumber > "+pageSize*(pageNumber-1);
		}else {
			try {
				throw new LqJdbcException("不支持这个驱动的分页:"+LqDBOperator.conf.getString("dataSource.driverClassName"));
			} catch (LqJdbcException e) {
				log.error(e);
				e.printStackTrace();
			}
		}
		
		List<ListOrderedMap> list =find(exeSql,obj);
		return new Page(startIndex, totalCount, pageSize, list,Page.DEFAULT_GROUP_PAGE_SIZE);
	}
	
	
	
	
	/**
	 * 分页《安全》<br>
	 * SQL_SERVER_2005分页:SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* from tableName
	 * @param sql
	 * @param pageNumber1 当前页数
	 * @param pageSize 一页所显示的记录数
	 * @param sqlCount 得总记录数SQL
	 * @return
	 */
	public  Page find(String sql,Integer pageNumber,Integer pageSize,String sqlCount,Object... obj){
		long totalCount=0;
		List listCount=find(sqlCount,obj);
		if (listCount.size()>0) {
			Map mapCount=(Map) listCount.get(0);
			if (mapCount.size()==1){
				totalCount=Long.valueOf(mapCount.values().toArray()[0].toString());
			}else {
				try {
					throw new LqJdbcException("查询总记录数的SQL有问题,只能返回一个数量,不能返回多个字段.\r\n"+"SQL:"+sqlCount);
				} catch (LqJdbcException e) {
					log.error(e);
					e.printStackTrace();
				}
			}
		}
		return find(sql,pageNumber,pageSize,totalCount,obj);
	}
	
	public  List findArrayList(String sql) throws SQLException {
		List list;
		try {
			List tableList=new ArrayList();
			list = new ArrayList();
			Object[] zdm=null;
			stmt = conn.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery(sql);
			ResultSetMetaData rsm = rs.getMetaData();// 获取数据库表结构
			int col = rsm.getColumnCount();// 获取数据库的列数
			while (rs.next()) {
				Object[] obj= new Object[col];
				zdm=new Object[col];
				for (int i = 1; i <= col; i++) {
					zdm[i-1]=rsm.getColumnName(i);
					obj[i-1]=rs.getObject(i);
				}
				list.add(obj);
			}
			if(zdm!=null){
				list.add(zdm);
			}
		}finally {
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
		return list;
	}
	

	
	/**
	 * 取总页数.
	 */
	private  long getTotalPageCount(long totalCount, int pageSize) {
		if (totalCount % pageSize == 0)
			return totalCount / pageSize;
		else
			return totalCount / pageSize + 1;
	}

	/**
	 * 传入oracle.sql.CLOB得到String
	 * @param clob
	 * @return
	 * @throws Exception
	 */
	public  String getClob(CLOB clob) throws Exception{
		Reader inStream = clob.getCharacterStream();    
        char[] c = new char[(int) clob.length()];    
        inStream.read(c);    
        //data是读出并需要返回的数据，类型是String    
        String data = new String(c);
        inStream.close();
        return data;
	}
	/**
	 * 更新CLOB
	 * @param clob
	 * @param data
	 * @throws Exception
	 */
	public  void setClob(CLOB clob,String data) throws Exception{
         Writer outStream = clob.getCharacterOutputStream();
         outStream.write(data);
	     outStream.flush();
	     outStream.close();
	}
	/**
	 * SERVLET读取BLOB
	 * @param args
	 * @throws Exception 
	 */
	/*public  void getBlob(Blob blob,HttpServletRequest request,HttpServletResponse response) throws Exception{
		InputStream in=blob.getBinaryStream();
        byte b[]=new byte[(int)blob.length()];
    	in.read(b);
    	response.reset();
    	javax.servlet.ServletOutputStream sout=response.getOutputStream();
    	sout.write(b);
		sout.flush();
		sout.close();
		in.close();
	}*/
	
	
	/**
	 * 得ID<MySql>
	 * @param tableName  表名
	 * @param userName	用户名
	 * @return
	 */
	public static synchronized long getMaxIdMySql(String tableName,String userName){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 日期格式化初始化
		String createDate=format.format(new Date());// 把当前日期格式化
		String sqlMax="select CASE when max(cast(bh as SIGNED)) is null then 1 ELSE max(cast(bh as SIGNED)) end m  from system_number where table_name=? ";
		Map mMax=Jdbc.find(sqlMax,new Object[]{tableName}).get(0);
		long l=Long.valueOf(mMax.get("m").toString())+1;
		int i=Jdbc.execute("insert into system_number (bh,table_name,create_user,create_date) values (?,?,?,?)",new Object[]{l,tableName,userName,createDate});
		return l;
	}
	
	/**
	 * 得ID<oracle>
	 * @param tablename
	 * @param username
	 * @return
	 */
	public  String getMaxIdOracle(String tablename,String username){
		String sql="" +
				"select lpad(to_char(nvl(max(to_number(id)), 0) + 1),\r\n" + 
				"            9,\r\n" + 
				"            0) maxid\r\n" + 
				"  from system_maxid" + 
				" where tablename = \'"+tablename+"\'"+
				"";
		List list=Jdbc.find(sql);
		Map map=(Map)list.get(0);
		String str=map.get("maxid").toString();
		sql="" +
			"insert into system_maxid\r\n" + 
			"  (id, tablename, createdate, createuser)\r\n" + 
			"values\r\n" + 
			"  (\'"+str+"\', \'"+tablename+"\', to_char(sysdate, \'yyyy-mm-dd hh24:mi:ss\'), \'"+username+"\')" +
			"";
		if(Jdbc.execute(sql)==0){
			getMaxIdOracle(tablename, username);
		}
		return str;
	}
	
	
	
	/**
	 * SelectTable动态加载
	 * @param sql
	 * @throws Exception
	 */
	public String findPageSelectTable(String sql,Integer pageSize,Long totalCount,Integer pageNumber,Object... obj) throws Exception {
		List list=find(sql, pageNumber, pageSize, totalCount, obj).getResult();
		String str=tabletostr(list);
		return str;
	}
	
	/**
	 * TABLE遍历为字符串
	 * 
	 * @param args
	 */
	public static String tabletostr(List list) {
		String str = "";
		if (list.size() < 1) {
			return str;
		}
		for (int i = 0; i < list.size(); i++) {
			Map map = (Map) list.get(i);
			Set<Entry<String, String>> entry = map.entrySet();
			Iterator<Entry<String, String>> it = entry.iterator();
			while (it.hasNext()) {
				Entry<String, String> config = it
						.next();
				// System.out.println(config.getKey()+" = "+config.getValue());
				if (config.getValue() == null) {
					str += config.getKey().toLowerCase() + "";
				} else {
					String z = "";
					try {
						z = config.getValue();
					} catch (Exception e) {
						z = config.toString().split("=")[1];
					}
					str += config.getKey().toLowerCase() + LqTzw.tzw7 + z;
				}
				if (it.hasNext() != false) {
					str += LqTzw.tzw2;
				}
			}
			if (i != list.size() - 1) {
				str += LqTzw.tzw1;
			}
		}
		return str;
	}
	
	/**
	 * TABLESTR转换成List
	 * @param zds
	 * @return
	 */
	public static List tabletostrmap(String tablestr){
		String[] datas=tablestr.split(LqTzw.tzw1);
		List list=new ArrayList();
		for (int i = 0; i < datas.length; i++) {
			String[] zds=datas[i].split(LqTzw.tzw2);
			Map map=new ListOrderedMap();
			for (int j = 0; j < zds.length; j++) {
				String[] temp=zds[j].split(LqTzw.tzw7);
				try {
					map.put(temp[0].toLowerCase(), temp[1]);
				} catch (ArrayIndexOutOfBoundsException e) {
					map.put(temp[0].toLowerCase(), "");
				}
			}
			list.add(map);
		}
		return list;
	}
	
	
	//创建语句类型；  
    private final int entitySave=0;
    private final int entityUpdate=1;
    private final int entityDelete=2;
	
	
    public int save(Object obj){
    	return setObject(obj,entitySave);
    }
    public int update(Object obj){
    	return setObject(obj, entityUpdate);
    }
    public int delete(Object obj){
    	return setObject(obj, entityDelete);
    }
    
    
    public int setObject(Object obj,int type){
    	String tableName="";//表名
    	//-----------------------------------插入--------------------------
 		String str1 = "";//有几个属性。
 		String str2 = "";//有几个待插入的位置
 		//-----------------------------------更新--------------------------
 		String strUpdateSql="";
 		String strUpdateWhereSql="";
 		Object strObjectUpdate = null;
 		//-----------------------------------删除--------------------------
 		String strDelWhereSql="";
 		Object strDelObject=null;
 		
 		List<Object> objList=new ArrayList();
    	Class cla=obj.getClass();
    	Annotation an=cla.getAnnotation(Table.class);
    	if (an==null) {
    		try {
				throw new LqJdbcException("没有给实体类"+obj+"配制注解@Table(name=)__name=表名");
			} catch (LqJdbcException e) {
				log.error(e);
				e.printStackTrace();
			}
			return 0;
		}
    	for (Method method : an.annotationType().getDeclaredMethods()) {
            if (!method.isAccessible()) {
                method.setAccessible(true);
            }
            Object invoke = null;
			try {
				invoke = method.invoke(an);
			} catch (IllegalAccessException e) {
				log.error(e);
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				log.error(e);
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				log.error(e);
				e.printStackTrace();
			}
			if (method.getName().equals("name")) {
				tableName=invoke+"";
			}
            //System.out.println("invoke methd " + method.getName() + " result:" + invoke);
//            if (invoke.getClass().isArray()) {
//                Object[] temp = (Object[]) invoke;
//                for (Object o : temp) {
//                    System.out.println(o);
//                }
//            }
        }
    	
        for (int i = 0; i < cla.getMethods().length; i++) {
        	Method m=cla.getMethods()[i];
        	String zdm=m.getName();
        	if (zdm.substring(0,3).equals("get")&&!zdm.equals("getClass")) {
        		if(type==entitySave){
        			Annotation anSql=m.getAnnotation(Sql.class);
        	    	if (anSql==null) {//没有配制不管它,不是字段
						continue;
					}
        			str1 += zdm.substring(3,zdm.length()) + ",";
        			str2 += "? ,";
        			try {
    					objList.add(m.invoke(obj));
    				} catch (IllegalAccessException e1) {
    					log.error(e1);
    					e1.printStackTrace();
    				} catch (IllegalArgumentException e1) {
    					log.error(e1);
    					e1.printStackTrace();
    				} catch (InvocationTargetException e1) {
    					log.error(e1);
    					e1.printStackTrace();
    				}
        		}else if(type==entityUpdate){
        			Annotation anSql=m.getAnnotation(Sql.class);
        	    	if (anSql==null) {//没有配制不管它,不是字段
						continue;
					}
        			Annotation anId=m.getAnnotation(Id.class);
        	    	if (anId!=null) {//ID
						strUpdateWhereSql+=zdm.substring(3,zdm.length())+"=?";
						try {
							strObjectUpdate=m.invoke(obj);
						} catch (IllegalAccessException e) {
							log.error(e);
							e.printStackTrace();
						} catch (IllegalArgumentException e) {
							log.error(e);
							e.printStackTrace();
						} catch (InvocationTargetException e) {
							log.error(e);
							e.printStackTrace();
						}
					}else {
						strUpdateSql+=zdm.substring(3,zdm.length())+"=?,";
						try {
							objList.add(m.invoke(obj));
						} catch (IllegalAccessException e1) {
							log.error(e1);
							e1.printStackTrace();
						} catch (IllegalArgumentException e1) {
							log.error(e1);
							e1.printStackTrace();
						} catch (InvocationTargetException e1) {
							log.error(e1);
							e1.printStackTrace();
						}
					}
        			
        		}else if(type==entityDelete){
        			Annotation anId=m.getAnnotation(Id.class);
        	    	if (anId!=null) {//ID
						strDelWhereSql+=zdm.substring(3,zdm.length())+"=?";
						try {
							strDelObject=m.invoke(obj);
						} catch (IllegalAccessException e) {
							log.error(e);
							e.printStackTrace();
						} catch (IllegalArgumentException e) {
							log.error(e);
							e.printStackTrace();
						} catch (InvocationTargetException e) {
							log.error(e);
							e.printStackTrace();
						}
					}
        		}
			}
		}
       
        
        if(type==entitySave){
        	// 拼成的字符串最后面多了一个逗号。所以截取除了最后一个字符之外的其他字符
        	str1 = str1.substring(0, str1.length() - 1);
        	str2 = str2.substring(0, str2.length() - 1);
        	//System.out.println(str1 + "  " + str2);
		}else if(type==entityUpdate){
			if (strObjectUpdate==null) {
				try {
					throw new LqJdbcException("没有给实体类"+obj+"中的ID配制注解@Id");
				} catch (LqJdbcException e) {
					log.error(e);
					e.printStackTrace();
				}
				return 0;
			}
			strUpdateSql = strUpdateSql.substring(0, strUpdateSql.length() - 1);
			objList.add(strObjectUpdate);
		}else if(type==entityDelete){
			if (strDelObject==null) {
				try {
					throw new LqJdbcException("没有给实体类"+obj+"中的ID配制注解@Id");
				} catch (LqJdbcException e) {
					log.error(e);
					e.printStackTrace();
				}
				return 0;
			}
			objList.add(strDelObject);
		}
    	int b=1;
    	String sql = null;
		if (type == entitySave) { // save
			sql = "insert into  " + tableName + " (" + str1 + ")   values (" + str2 + ")";
		} else if (type == entityUpdate) { // update
			sql = "update " + tableName + " set "+strUpdateSql+" where "+strUpdateWhereSql; 
		} else if (type == entityDelete) {// delete
			sql = "delete from " + tableName + "  where  "+strDelWhereSql;
		}
		log.info(sql);
    	try {
	        java.sql.PreparedStatement  ps= conn.prepareStatement(sql);  
	        for(int i=0;i<objList.size();i++){
	           ps.setObject(i+1,objList.get(i));
	        }
	        ps.executeUpdate();  
	        shiB=1;
    	}catch(Exception e){
    		b=0;
    		try {conn.rollback();} catch (SQLException e1) {
    			log.error(e1);
    			e1.printStackTrace();
    		}
    		log.error(e);
			e.printStackTrace();
    	}
    	return b;
    }
    
	
    private static <T> T executeResultSet(Class<T> cls, ResultSet rs) throws InstantiationException, IllegalAccessException, SQLException {
        T obj = cls.newInstance();
        ResultSetMetaData rsm = rs.getMetaData();
        int columnCount = rsm.getColumnCount();
        Field[] fields = cls.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            field.setAccessible(true);//类中的成员变量为private,故必须进行此操作
            String fieldName = field.getName();
            for (int j = 1; j <= columnCount; j++) {
                String columnName = rsm.getColumnName(j);
                if (fieldName.equalsIgnoreCase(columnName)) {
                    Object value = rs.getObject(j);
                    field.set(obj, value);
                    break;
                }
            }
        }
        return obj;
    }
	
}
